# Copyright 2001-2007 by iNeed,Inc. All Rights Reserved.
#-*- encoding:utf-8 -*-
"""
user (id,phone,pwd,email,trueName,sex,age)
driver (id,licenseId,company,carId,carBrand,carColor,credict,phone)
TBD:  passanger(id,userId)
taxiService (id,x,y,address,departureTime,timeout,driverId)
location(id,phone,x,y,address,time)
subscription(id,x,y,address,departureTime,timeout,phone)


"""
import sys, os, time, string,sqlite3
import sock_asyncore
from common import *
__author__  = "Number Huang<huang.bafeng@iNeed.com>"
__status__  = "developing"
__version__ = "0.1109.6.1"


_defaultPath = "E:/kingcloud/ineed-taxi/code/db/iNeedDB"

SPECIAL_CMD_ORDER="order111"
SPECIAL_CMD=(SPECIAL_CMD_ORDER,)
gSqlite3Engine=None
"""
The limitaion of SQLITE3 is it only supports single thread,
so using another taskEngine(thread) to make sure it is in the thread
"""
class OpenSQLITE3(Task):
    """
        class comment
    """

    def __init__(self,owner,name="OpenSQLITE3",timeIntervalBySec=0,loopCount=1):
        super(OpenSQLITE3,self).__init__(owner,name,timeIntervalBySec,loopCount)


    def run(self):
        self.db = iNeedDB(_defaultPath)
        self.db.open()

class CloseSQLITE3(Task):
    """
        class comment
    """

    def __init__(self,owner,db,name="CloseSQLITE3",timeIntervalBySec=0,loopCount=1):
        super(CloseSQLITE3,self).__init__(owner,name,timeIntervalBySec,loopCount)
        self.db = db

    def run(self):

        self.db.close()


class SQLITE3Task(Task):
    """
        class comment
    """

    def __init__(self,method,params,owner,name="SQLITE3Task",timeIntervalBySec=0,loopCount=1):
        super(SQLITE3Task,self).__init__(owner,name,timeIntervalBySec,loopCount)
        self.method = method
        self.params = params

    def run(self):

        self.result = self.method(self.params)

class SQLITE3Job_UpdateOC(Task):
    """
        This job updates the orderCount of driverstatus
        every 5 seconds
        TODO: for real use, like in mysql, we need to set a job at db level
    """

    def __init__(self,owner,name="SQLITE3Job_UpdateOC",timeIntervalBySec=5,loopCount=-1):
        super(SQLITE3Job_UpdateOC,self).__init__(owner,name,timeIntervalBySec,loopCount)
        self.db = owner.db
        
    def run(self):
        #TODO: this is test level code,opt in future
        driverList = self.db.query("driverstatus",{"status=":ES_IDLE})
        last24Hours=time.time()-24*3600
        condition={"createTime>":last24Hours}
        for driver in driverList:
            condtion["driverID"]=driver[0]
            orderCount = len(self.db.query("order1",condition))
            self.db.update("driverstatus",{"orderCount":orderCount},{"phone=":driver[0]})
        
class SQLITE3Engine(TaskEngine):
    """
        class comment
    """

    def __init__(self,owner,name="SQLITE3Engine",hang=False):

        super(SQLITE3Engine,self).__init__(owner,name,hang)
        task = OpenSQLITE3(self)
        self.addTask(task)
        while(not task.isLoopEnd()):
            time.sleep(0.001)
        self.db = task.db
        task = SQLITE3Job_UpdateOC(self)
        #TODO: launch it when the match strategy is done
        #self.addTask(task) 
        
    def _execute1(self,method,params):
        task = SQLITE3Task(method,params,self)
        self.addTask(task)
        while(not task.isLoopEnd()):
            time.sleep(0.001)
        return task.result

    def insert(self,table,dict):
        self._execute1(self.db.insert,(table,dict))

    def fetchall(self,table,dict,field='*'):
        result = self._execute1(self.db.query,(table,dict,field))
        return result

    def delete(self,table,dict):
        self._execute1(self.db.delete,(table,dict))

    def update(self,table,dict,condition):
        self._execute1(self.db.update,(table,dict,condition))
    
    def execute(self,sql):
        self._execute1(self.db.execute,sql)
        
    def exit(self):
        if self.db:
            task = CloseSQLITE3(self,self.db)
            self.addTask(task)
            while(not task.isLoopEnd()):
                time.sleep(0.001)
        super(SQLITE3Engine,self).exit()


def CreateDB(owner=None):
    if not owner:
        owner = FakeOwner()
    global gSqlite3Engine
    if not gSqlite3Engine:
       gSqlite3Engine = SQLITE3Engine(owner)

    return gSqlite3Engine

class iNeedDB(object):
    """
        class comment
    """

    def __init__(self,path,owner=None):
        self.dbPath = path
        self.conn = None
        if owner:
            self.log = owner.log
        else:
            self.log = logging#.LoggerAdapter(logging.getLogger("iNeedTaxi"),{"ip" : "database"})
    def open(self):

        self.conn = sqlite3.connect(self.dbPath)

    def insert(self,params):
        table,dict = params
        str1 = []
        str2 = []
        values=[]
        for key,value in dict.iteritems():
            str1.append(key+",")
            str2.append("?,")
            values.append(value)
        str2[-1]="?"
        str1[-1]=key
        sql = "insert into %s("%table
        sql = sql + string.join(str1) + ") values (" + string.join(str2)+")"
        values = tuple(values)
        
        return self._execute2(sql, values)

            #self.log.error("Add user fail:%s"%traceback.format_exc())

    def query(self,params):
        """
        only support the query by single key
        """
        table,dict,field = params
        specailCMD=" "
        condition =" "
        if dict:
            str1 = []
            values=[]
            for key,value in dict.iteritems():
                if key in SPECIAL_CMD:
                    specailCMD+=value+" "
                else:
                    str1.append(key+"? and")
                    values.append(value)
            if len(str1):
                str1[-1]= key + "?"
                condition +=" where " + string.join(str1)
            sql = "select %s from "%field + table + condition +specailCMD
            values = tuple(values)
            
            return self._execute2(sql,values).fetchall()
        else:
            sql = "select * from " + table
            with self.conn:
                return self.conn.execute(sql).fetchall()


    def delete(self,params):

        """
        only support the query by single key
        """
        table,dict = params
        sql = "delete from " + table + " where " + dict.keys()[0] + "=?"
        key =(dict.values()[0],)
        
        return self._execute2(sql,key )

    def update(self,params):
        table,dict,condition = params
        sql = "update " + table + " set "
        str1=[]
        values=[]
        for key,value in dict.iteritems():
            str1.append(key + "=?,")
            values.append(value)
        str1[-1] = key +"=? "
        sql += string.join(str1) + "where " + condition.keys()[0] + "=?"
        values.append(condition.values()[0])
        values = tuple(values)
        
        return self._execute2(sql, values)
    def _execute2(self,sql,param):
        with self.conn:
            try:
                return self.conn.execute(sql, param)
            except Exception,e:
                self.log.error("%s"%(sql+str(param)))
                raise
    def execute(self,sql):
        with self.conn:
             return self.conn.execute(sql)

    def close(self):

        self.conn.close()

def registerUser(phone,pwd,email,trueName,sex,age):
    """
    register the user and return the userID if succeed, else return -1
    """
    db = CreateDB()
    row1 = {"phone":phone, "pwd":pwd,"email":email,"trueName":trueName,"sex":sex,"age":age}
    db.insert("user",row1)
    """
    row = db.query("user",{"phone=":phone}).fetchall()
    if row:
        self.log.debug("regist User success: %s"%row)
        return row[0][0]
    else:
        self.log.error("regist User fail")
        return -1
    """
def registerDriver(phone,pwd,email,trueName,sex,age,licenseId,company,carId,carBrand,carColor,credict=0):
    """
        register the driver and return the userID if succeed, else return -1
    """
    db = CreateDB()
    registerUser(phone,pwd,email,trueName,sex,age)


    row1 = {"licenseId":licenseId,"company":company,"carId":carId,"carBrand":carBrand,"carColor":carColor,"credict":credict,"phone":phone}
    db.insert("driver",row1)
    """
    row = db.query("driver",{"phone=":phone}).fetchall()
    if row:
        self.log.debug("regist driver success: %s"%row)
        return row[0][0]
    else:
        self.log.error("regist driver fail")
        return -1
    """
def updateLocation(db,role,locationParams):
    condition = {"phone":locationParams["From"]}
    del locationParams["From"]
    db.update(locationTable[role],locationParams,condition)

def insertLocation(db,role,locationParams):
    locationParams["phone"]=locationParams["From"]
    del locationParams["From"] 
    db.insert(locationTable[role],locationParams)


OMTable_driver={
    "licenseId":0,
    "company"  :1,
    "carId"    :2,
    "carBrand" :3,
    "carColor" :4,
    "credict"  :5,
    "phone"    :6
         
         }

OMTable_driverstatus={
    "phone"           :0,
    "x"               :1,
    "y"               :2,
    "address"         :3,
    "time"            :4,
    "status"          :5,
    "nextTime"        :6, 
    "orderCount"      :7          
                 
                 }

OMTable_order1={
    "beginTime"     :0,
    "endTime"       :1,
    "driverID"      :2,
    "passengerID"   :3,
    "pubID"         :4,
    "status"        :5,
    "price"         :6, 
    "type"          :7,
    "number"        :8,
    "score"         :9,
    "comment"       :10                  
                      }

OMTable_passengerstatus={
    "phone"           :0,
    "x"               :1,
    "y"               :2,
    "address"         :3,
    "time"            :4,
    "status"          :5            
                }

OMTable_taxiservice={
    "pubID"                 :0,
    "address"               :1,
    "departureTime"         :2,
    "timeout"               :3,
    "phone"                 :4,
    "x"                     :5,
    "y"                     :6,
    "status"                :7,
    "address2"              :8,
    "destinationTime"       :9,
    "x2"                    :10,
    "y2"                    :11,
    "pricelow"              :12,
    "pricehigh"             :13,
    "type"                  :14,
    "execTime"              :15,
    "poolingID"             :16,
    "orderID"               :17,
    "number"                :18,
    "refRouting"            :19,
    "refFee"                :20,
    "refTime"               :21,
    "gridx"                 :22,
    "gridy"                 :23,
    "gridx2"                :24,
    "gridy2"                :25,               
    "driverID"              :26, 
    "refRouting2"           :27                    
                         }

OMTable_user={
    "phone"                :0,
    "pwd"                  :1,
    "trueName"             :2,
    "email"                :3,
    "sex"                  :4,
    "age"                  :5    
              }

if __name__=="__main__":
    moduleName="__main__"
else:
    moduleName="database"

def getItemIdx(table,field):  
    name = "OMTable_"+table
    mpTable = getattr(sys.modules[moduleName], name)
    idx     = mpTable[field]
    return  idx
 
def getItem(table,field,row):
    name = "OMTable_"+table
    mpTable = getattr(sys.modules[moduleName], name)
    idx     = mpTable[field]
    return row[idx]

def getAllItem(table,row):
     destDict={}
     name = "OMTable_"+table
     mpTable = getattr(sys.modules[moduleName], name)
     for key,value in mpTable.items():
            destDict[key] = row[value]
     return  destDict


def test1():
    import configall
    print configall.SHORTTIME_LIMIT       
if __name__=="__main__":

    row=("321","dazhong","B230","Pasate","red",5,"13817794778")
    print getItem("driver","phone",row)
    db = CreateDB()
    row1={ 
    "phone"           :"13917784778",
    "x"               :12,
    "y"               :23,
    "address"         :"sdf",
    "time"            :time.time(),
    "status"          :0,
    "nextTime"        :time.time(), 
    "orderCount"      :7          
    }

    #db.insert("driverstatus",row1)
    condition = {SPECIAL_CMD_ORDER:"order by orderCount"}
    print db.fetchall("driverstatus",condition) 
  
    """
    #registerDriver("13817794778","fdd","hbfhero@sina.com","Bafeng Huang","Male",1,"400805","Herz-taxi","5055","Ford","black",0)
    condition={"time<":time.time()-900}
    db.delete("driverstatus",condition)
    #rows = db.fetchall("user",None)#,{"sex=":1,"age>":18})
    order = db.fetchall("order1",{"pubID=":68})[0]
    list1 = [phone for phone in row for row in rows]
    for row in rows:
        print row
    db.exit()
    """
    """
    registerUser("18917900804","fdd","dyy@hotmail.com","Yuying Du","Female",60)
    registerUser("13627494153","fdd","fzs@hotmail.com","Zhaoshen Fan","Male",57)
    registerUser("13487578217","fdd","lss@hotmail.com","Shoushu Li","Female",56)
    registerDriver("13917841776","fdd","fdd@hotmail.com","Danie Fan","Female",1,"400705","Bus-taxi","2005","Polo","red",0)
    registerDriver("13917794778","fdd","h8f@hotmail.com","Number Huang","Male",1,"400705","Johnson-taxi","1024","Santana 3000","yellow",0)
    registerDriver("18917900802","fdd","hxy@hotmail.com","Smile Huang","Male",1,"300705","Dazhong-taxi","5899","Pasate","white",0)
        db = CreateDB()
        row = db.query("user",{"phone":"13817794778"}).fetchall()
        print row[0]
        if row:
            self.log.debug("regist User success: %s"%row)
            print row[0][0]
        db.delete("user",{"phone":"13817794778"})
        #row1={"pwd":"hxy","email":"h8f@hotmail.com","trueName":"Number Huang","sex":"Male","age":33}
        #db.insert("user",row1)
        row1={"pwd":"hxy2","email":"h8f@hotmail.com"}
        condition={"sex":"Male"}
        db.update("user",row1,condition)
        row = db.query("user",{"phone":"13817794778"})
        print row.fetchall()
        condition={"sex":"FeMale"}
        db.update("user",row1,condition)
        row = db.query("user",{"phone":"13817794778"})
        print row.fetchall()
        row1={"phone":"13817794779","pwd":"hxy","email":"h8f@hotmail.com","trueName":"Number Huang","sex":"Male","age":33}
        db.insert("user",row1)
        row = db.query("user",{"phone":"13817794779"})
        print row.fetchall()
        db.delete("user",{"phone":"13817794779"})
        row = db.query("user",{"phone":"13817794779"})
        print row.fetchall()
    """


